package jp.co.dailyReport.total.sql;

import java.util.Calendar;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.log4j.Logger;
import jp.co.dailyReport.Executer;
import jp.co.dailyReport.total.TotalHoursWorkedStaffCondition;

/**
 * 社員毎稼働時間集計結果件数を取得するクラスです。
 */
public class TotalHoursWorkedStaffCountExecuter implements Executer {

	private static Logger log = Logger.getLogger(TotalHoursWorkedStaffCountExecuter.class);

	private StringBuffer sqlStatement;
	private List searchObjectList;
	private Integer staffId;
	private Integer employmentSystem;
	private Integer projectId;
	private Integer workItemId;
	private java.sql.Date startWorkDate;
	private java.sql.Date endWorkDate;

	public TotalHoursWorkedStaffCountExecuter(TotalHoursWorkedStaffCondition totalHoursWorkedStaffCondition) {
		searchObjectList = new ArrayList();
		sqlStatement = new StringBuffer();
		if(totalHoursWorkedStaffCondition.getStaffId()!=0) 
			setStaffId(totalHoursWorkedStaffCondition.getStaffId());
		if(totalHoursWorkedStaffCondition.getEmploymentSystem()!=-1) 
			setEmploymentSystem(totalHoursWorkedStaffCondition.getEmploymentSystem());
		if(totalHoursWorkedStaffCondition.getProjectId()!=0) 
			setProjectId(totalHoursWorkedStaffCondition.getProjectId());
		if(totalHoursWorkedStaffCondition.getWorkItemId()!=0) 
			setWorkItemId(totalHoursWorkedStaffCondition.getWorkItemId());
		if(totalHoursWorkedStaffCondition.getStartWorkDate()!=null) 
			setStartWorkDate(totalHoursWorkedStaffCondition.getStartWorkDate());
		if(totalHoursWorkedStaffCondition.getEndWorkDate()!=null) 
			setSearchWorkDateEnd(totalHoursWorkedStaffCondition.getEndWorkDate());
	}

	private void appendAND(String str){
		if(sqlStatement.length() > 0)
			sqlStatement.append(" AND " + str);
		else
			sqlStatement.append(str);
	}

	private void appendOR(String str){
		if(sqlStatement.length() > 0)
			sqlStatement.append(" OR " + str);
		else
			sqlStatement.append(str);
	}

	public void setStaffId(int staffId) {
		this.staffId = new Integer(staffId);
		searchObjectList.add(this.staffId);
		appendAND("s.staffId = ?");
	}

	public void setEmploymentSystem(int employmentSystem) {
		this.employmentSystem = new Integer(employmentSystem);
		searchObjectList.add(this.employmentSystem);
		appendAND("s.employmentForm = ?");
	}

	public void setProjectId(int projectId) {
		this.projectId = new Integer(projectId);
		searchObjectList.add(this.projectId);
		appendAND("p.projectId = ?");
	}

	public void setWorkItemId(int workItemId) {
		this.workItemId = new Integer(workItemId);
		searchObjectList.add(this.workItemId);
		appendAND("w.workItemId = ?");
	}

	public void setStartWorkDate(Calendar startWorkDate){
	  this.startWorkDate = new java.sql.Date(startWorkDate.getTime().getTime());
	  this.searchObjectList.add(this.startWorkDate);
	  appendAND("d.workDate >= ?");
	}

	public void setSearchWorkDateEnd(Calendar endWorkDate){
	  this.endWorkDate = new java.sql.Date(endWorkDate.getTime().getTime());
	  this.searchObjectList.add(this.endWorkDate);
	  appendAND("d.workDate <= ?");
	}

	/**
	 * 設定された条件を基に、社員毎稼働時間集計結果件数を取得します。
	 * 
	 * @param conn コネクションオブジェクト
	 * @return 社員毎稼働時間集計結果件数
	 * @throws SQLException
	 */
	public Object[] execute(Connection conn) throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Integer answer = null;

		String sql = "SELECT count(DISTINCT dw.dailyReportWorkItemId) AS count " +
					 "FROM dailyReport AS d " +
					 "LEFT JOIN dailyReportWorkItem AS dw ON d.dailyReportId = dw.dailyReportId " +
					 "LEFT JOIN workItem AS w ON dw.workItemId = w.workItemId " +
					 "LEFT JOIN project AS p ON w.projectId = p.projectId " +
					 "LEFT JOIN staff AS s ON d.staffId = s.staffId";

		if(sqlStatement.length() > 0){
			sql = sql + " where " + sqlStatement.toString();
		}
		log.debug("sql: " + sql);

        try {
			pstmt = conn.prepareStatement(sql);
			if(this.staffId != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.staffId)+1, this.staffId.intValue());
				releaseSearchObject(this.staffId);
			}
			if(this.employmentSystem != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.employmentSystem)+1, this.employmentSystem.intValue());
				releaseSearchObject(this.employmentSystem);
			}
			if(this.projectId != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.projectId)+1, this.projectId.intValue());
				releaseSearchObject(this.projectId);
			}
			if(this.workItemId != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.workItemId)+1, this.workItemId.intValue());
				releaseSearchObject(this.workItemId);
			}
			if(this.startWorkDate != null){
				pstmt.setDate(this.searchObjectList.indexOf(this.startWorkDate)+1, this.startWorkDate);
				releaseSearchObject(this.startWorkDate);
			}
			if(this.endWorkDate != null){
				pstmt.setDate(this.searchObjectList.indexOf(this.endWorkDate)+1, this.endWorkDate);
				releaseSearchObject(this.endWorkDate);
			}
			rset = pstmt.executeQuery();
			if (rset.next()) {
				answer = new Integer(rset.getInt("count"));
			} else {
				throw new SQLException("no data found");
			}
		} finally {
			if(rset != null)
				rset.close();
			if(pstmt != null)
				pstmt.close();
		}
		return new Integer[] { answer };
	}

	private void releaseSearchObject(Object obj){
		searchObjectList.set(searchObjectList.indexOf(obj), null);
	}
}
